﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class UserFileCabinettbDAO
    {
        /// <summary>
        /// 保存个人文件柜的文件信息
        /// </summary>
        /// <param name="fileTitle"></param>
        /// <param name="fileContent"></param>
        /// <param name="FilePath"></param>
        /// <param name="FileUploadTime"></param>
        /// <param name="UserLoginName"></param>
        /// <returns></returns>
        public int saveFileCabinetFile(string fileTitle, string fileContent, string filePath, DateTime fileUploadTime, string userLoginName)
        {
            int fileId = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into UserFileCabinet values(@fileTitle, @fileContent, @FilePath, @FileUploadTime, @UserLoginName, default)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@fileTitle", fileTitle));
                sqlCmd.Parameters.Add(new SqlParameter("@fileContent", fileContent));
                sqlCmd.Parameters.Add(new SqlParameter("@FilePath", filePath));
                sqlCmd.Parameters.Add(new SqlParameter("@FileUploadTime", fileUploadTime));
                sqlCmd.Parameters.Add(new SqlParameter("@UserLoginName", userLoginName));
                sqlCmd.ExecuteNonQuery();
                sql = "select @@identity";
                sqlCmd = new SqlCommand(sql, sqlConn);
                fileId = int.Parse(sqlCmd.ExecuteScalar().ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return fileId;
        }

        /// <summary>
        /// 根据用户登录名查找用户个人文件柜的记录信息
        /// </summary>
        /// <param name="UserLoginName"></param>
        /// <returns></returns>
        public List<UserFileCabinettb> findByUserLoginName(string userLoginName)
        {
            List<UserFileCabinettb> list = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserFileCabinet where UserLoginName = @UserLoginName";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@UserLoginName", userLoginName));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserFileCabinettb>();
                    UserDAO userDAO = new UserDAO();
                    while (sqlDr.Read())
                    {
                        UserFileCabinettb userFileCabinet = new UserFileCabinettb();
                        userFileCabinet.FileId = (int)sqlDr["FileId"];
                        userFileCabinet.FileTitle = sqlDr["FileTitle"].ToString();
                        userFileCabinet.FilePath = sqlDr["FilePath"].ToString();
                        userFileCabinet.FileContent = sqlDr["FileContent"].ToString();
                        userFileCabinet.FileUploadTime = (DateTime)sqlDr["FileUploadTime"];
                        userFileCabinet.CurUser = userDAO.FindByLoginName(sqlDr["UserLoginName"].ToString());
                        userFileCabinet.ReadState = (int)sqlDr["ReadState"];
                        list.Add(userFileCabinet);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据记录编号查找记录信息
        /// </summary>
        /// <param name="fid"></param>
        /// <returns></returns>
        public UserFileCabinettb findByFileId(int fid)
        {
            UserFileCabinettb userFileCabinet = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserFileCabinet where FileId = @FileId";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@FileId", fid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    sqlDr.Read();
                    userFileCabinet = new UserFileCabinettb();
                    UserDAO userDAO = new UserDAO();
                    userFileCabinet.FileId = (int)sqlDr["FileId"];
                    userFileCabinet.FileTitle = sqlDr["FileTitle"].ToString();
                    userFileCabinet.FilePath = sqlDr["FilePath"].ToString();
                    userFileCabinet.FileContent = sqlDr["FileContent"].ToString();
                    userFileCabinet.FileUploadTime = (DateTime)sqlDr["FileUploadTime"];
                    userFileCabinet.CurUser = userDAO.FindByLoginName(sqlDr["UserLoginName"].ToString());
                    userFileCabinet.ReadState = (int)sqlDr["ReadState"];
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return userFileCabinet;
        }

        /// <summary>
        /// 根据记录编号删除记录信息
        /// </summary>
        /// <param name="fid"></param>
        /// <returns></returns>
        public int delFileCabinetInfoByFileId(int fid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from UserFileCabinet where FileId = @FileId";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@FileId", fid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据文件路径和当前用户登录名查找个人文件柜信息
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="UserLoginName"></param>
        /// <returns></returns>
        public List<UserFileCabinettb> findByFilePathAndUserLoginName(string filePath, string userLoginName)
        {
            List<UserFileCabinettb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserFileCabinet where FilePath = @FilePath and UserLoginName = @UserLoginName";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@FilePath", filePath));
                sqlCmd.Parameters.Add(new SqlParameter("@UserLoginName", userLoginName));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserFileCabinettb>();
                    UserDAO userDAO = new UserDAO();
                    while (sqlDr.Read())
                    {
                        UserFileCabinettb userFileCabinet = new UserFileCabinettb();
                        userFileCabinet.FileId = (int)sqlDr["FileId"];
                        userFileCabinet.FileTitle = sqlDr["FileTitle"].ToString();
                        userFileCabinet.FilePath = sqlDr["FilePath"].ToString();
                        userFileCabinet.FileContent = sqlDr["FileContent"].ToString();
                        userFileCabinet.FileUploadTime = (DateTime)sqlDr["FileUploadTime"];
                        userFileCabinet.CurUser = userDAO.FindByLoginName(sqlDr["UserLoginName"].ToString());
                        userFileCabinet.ReadState = (int)sqlDr["ReadState"];
                        list.Add(userFileCabinet);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
    }
}
